/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package framework.db;

import framework.config.Database;
import framework.data.ReservaQuarto;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Thairony
 */
public class ReservaQuartoManager
{
    private static ReservaQuartoManager instance;

    /**
     * 
     * @return 
     */
    public static ReservaQuartoManager getInstance()
    {
        if ( instance == null )
        {
            instance = new ReservaQuartoManager();
        }

        return instance;
    }
    
    /**
     * 
     * @return
     * @throws Exception 
     */
    private Database getDatabase() throws Exception
    {
        return Database.getInstance();
    }
    
    /**
     * 
     * @param locacao quarto
     * @throws Exception 
     */
    public void addReservaQuarto( ReservaQuarto reservaQuarto ) throws Exception
    {
        Database db = getDatabase();
        
        try
        {
            String sql = "insert into locacao_quarto (" + 
                    " codigo, " +
                    " cd_quarto, " +
                    " cd_cadastro, " +
                    " valor, " +
                    " excesso, " +
                    " check_in, " +
                    " check_out " +
                    " ) values ( " +
                    " ?, ?, ?, ?, ?, ?, ? )";

            PreparedStatement ps = db.prepareStatement( sql );
            
            int i = 1;
            ps.setInt( i++, reservaQuarto.getCodigo() );
            ps.setInt( i++, reservaQuarto.getQuarto() );
            ps.setInt( i++, reservaQuarto.getCadastro() );
            ps.setDouble( i++, reservaQuarto.getValor() );
            ps.setDouble( i++, reservaQuarto.getExtra() );
            ps.setDate( i++, reservaQuarto.getCheckIn() );
            ps.setDate( i++, reservaQuarto.getCheckOut() );
            
            ps.execute();
        }
        
        finally
        {
            db.close();
        }           
    }
    
    /**
     * 
     * @param locacao quarto
     * @throws Exception 
     */
    public void updateReservaQuarto( ReservaQuarto reservaQuarto ) throws Exception
    {
        Database db = getDatabase();
        
        try
        {
            String sql = "update locacao_quarto set " + 
                    " cd_quarto = ?," +
                    " cd_cadastro = ?," +
                    " valor = ?," +
                    " excesso = ?," +
                    " check_in = ?," +
                    " check_out = ? " +
                    " where " +
                    " codigo = ? ";

            PreparedStatement ps = db.prepareStatement( sql );
            
            int i = 1;
            ps.setInt( i++, reservaQuarto.getQuarto() );
            ps.setInt( i++, reservaQuarto.getCadastro() );
            ps.setDouble( i++, reservaQuarto.getValor() );
            ps.setDouble( i++, reservaQuarto.getExtra() );
            ps.setDate( i++, reservaQuarto.getCheckIn() );
            ps.setDate( i++, reservaQuarto.getCheckOut() );
            ps.setInt( i++, reservaQuarto.getCodigo() );
            
            ps.execute();
        }
        
        finally
        {
            db.close();
        }           
    }
    
    /**
     * 
     */
    public void deleteReservaQuarto( ReservaQuarto reservaQuarto ) throws Exception
    {
        Database db = getDatabase();
        
        try
        {
            String sql = "delete from locacao_quarto " + 
                    " where " +
                    " codigo = ? ";

            PreparedStatement ps = db.prepareStatement( sql );
            
            int i = 1;
            ps.setInt( i++, reservaQuarto.getCodigo() );
            
            ps.execute();
        }
        
        finally
        {
            db.close();
        }           
    }
    
    /**
     * 
     * @return
     * @throws Exception 
     */
    public List<ReservaQuarto> getReservaQuarto() throws Exception
    {
        Database db = getDatabase();
        
        try
        {
            String sql = Selects.SELECT_LOCACAOQUARTO;
            
            PreparedStatement ps = db.prepareStatement( sql );
            
            return fetchReservaQuartos( ps );
        }
        
        finally
        {
            db.close();
        }
    }
    
    /**
     * 
     * @param codigo
     * @return
     * @throws Exception 
     */
    public ReservaQuarto getReservaQuarto( int codigo ) throws Exception
    {
        Database db = getDatabase();
        
        try
        {
            String sql = Selects.SELECT_LOCACAOQUARTO +
                    " where " +
                    " codigo = ?";
            
            PreparedStatement ps = db.prepareStatement( sql );
            
            ps.setInt( 1, codigo );
            
            return fetchReservaQuarto( ps );
        }
        
        finally
        {
            db.close();
        }
    }
    
    /**
     * 
     * @param st
     * @return
     * @throws Exception 
     */
    private List<ReservaQuarto> fetchReservaQuartos( PreparedStatement st ) throws Exception
    {
        ResultSet rs = st.executeQuery();
        
        List<ReservaQuarto> usuarios = new ArrayList<ReservaQuarto>();
        
        while ( rs.next() )
        {
            ReservaQuarto locacaoquarto = new ReservaQuarto();
        
            int i = 1;
            locacaoquarto.setCodigo( rs.getInt( i++ ) );
            locacaoquarto.setQuarto( rs.getInt( i++ ) );
            locacaoquarto.setCadastro( rs.getInt( i++ ) );
            locacaoquarto.setValor( rs.getDouble( i++ ) );
            locacaoquarto.setExtra( rs.getDouble( i++ ) );
            locacaoquarto.setCheckIn( rs.getDate( i++ ) );
            locacaoquarto.setCheckOut( rs.getDate( i++ ) );

            usuarios.add( locacaoquarto );
        }
        
        return usuarios;
    }
    
    /**
     * 
     * @param st
     * @return
     * @throws Exception 
     */
    private ReservaQuarto fetchReservaQuarto( PreparedStatement st ) throws Exception
    {
        ResultSet rs = st.executeQuery();
        
        rs.next();
        
        ReservaQuarto locacaoquarto = new ReservaQuarto();
        
        int i = 1;
        locacaoquarto.setCodigo( rs.getInt( i++ ) );
        locacaoquarto.setQuarto( rs.getInt( i++ ) );
        locacaoquarto.setCadastro( rs.getInt( i++ ) );
        locacaoquarto.setValor( rs.getDouble( i++ ) );
        locacaoquarto.setExtra( rs.getDouble( i++ ) );
        locacaoquarto.setCheckIn( rs.getDate( i++ ) );
        locacaoquarto.setCheckOut( rs.getDate( i++ ) );
        
        return locacaoquarto;
    }
}
